package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.entity.Employee;
import com.util.DBHelp;
import com.util.RowMapper;

public class EmployeeDao {

	DBHelp<Employee> help=new DBHelp<Employee>();
	public Employee login(String username, String password) {
		String sql="SELECT id,username,`password`,pic FROM t_employee where username=? and `password`=?";
		return help.executeQueryForObject(sql, new Mapper(), username,password);
	}
	
	private class Mapper implements RowMapper<Employee>{

		public Employee mapperRow(ResultSet rs) throws SQLException {
			Employee employee=new Employee();
			employee.setId(rs.getString("id"));
			employee.setUsername(rs.getString("username"));
			employee.setPassword(rs.getString("password"));
			employee.setPic(rs.getString("pic"));
			
			return employee;
		}
		
	}

	public List<Employee> getAll() {
		String sql="SELECT id,`username`,`password`,pic FROM t_employee";
		return help.executeQueryForList(sql, new Mapper());
	}

	public Employee getByName(String string) {
		String sql="SELECT id,`username`,`password`,pic FROM t_employee where `username`=?";
		return help.executeQueryForObject(sql, new Mapper(),string);
	}

	public List<Employee> getWorkers(String id) {
		String sql="SELECT id,`username`,`password`,pic FROM t_employee WHERE id IN(SELECT employeeid FROM t_employee_project WHERE projectid=? AND role='worker')";
		return help.executeQueryForList(sql, new Mapper(), id);
	}

	public Employee getAdmin(String id) {
		String sql="SELECT id,`username`,`password`,pic FROM t_employee WHERE id=(SELECT employeeid FROM t_employee_project WHERE projectid=? AND role='admin')";
		return help.executeQueryForObject(sql, new Mapper(), id);
	}
	
	public Employee getWorker(String id) {
		String sql="SELECT id,`username`,`password`,pic FROM t_employee WHERE id=?";
		return help.executeQueryForObject(sql, new Mapper(), id);
	}

	public Employee getById(String id) {
		String sql="SELECT `id`,`username`,`password`,pic FROM `t_employee` WHERE id=?";
		return help.executeQueryForObject(sql, new Mapper(), id);
	}

	public void add(Employee employee) {
		String sql="INSERT INTO `t_employee`(id,`username`,`password`,pic) VALUES(?,?,?,?)";
		help.executeSQL(sql,employee.getId(), employee.getUsername(),employee.getPassword(),employee.getPic());
	}

	public void update(Employee employee) {
		String sql="UPDATE `t_employee` SET `id`=?,`username`=?,`password`=?,`pic`=? WHERE id=?";
		help.executeSQL(sql, employee.getId(),employee.getUsername(),employee.getPassword(),employee.getPic(),employee.getId());
	}
	
}
